package com.face.sql.jsu;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.IdentityHashMap;
import java.util.List;
import java.util.Map;

import com.face.bean.jsu.UsernameAndName;

public class LostDao {
	
	Connection con;
	ResultSet  rs;
	PreparedStatement pstmt;
	
	/*
	 * 向表格中插入数据
	 * 参数为faceid和path
	 */
	public void insertData(String username,String name,String faceid,String path) {
		String sql = "INSERT INTO lost_population (username,name,faceid,facepath) VALUES(?,?,?,?)"; 
		try{
			con = JDBCUtil.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, username);
			pstmt.setString(2, name);
			pstmt.setString(3, faceid);
			pstmt.setString(4, path);
			int rtn = pstmt.executeUpdate();
			System.out.println("插入成功");
		}catch(SQLException e){
			
		}finally{
			JDBCUtil.CloseConnection(rs, pstmt, con);
		}
		
	}
	/*
	 * 通过faceid唯一标识查询上传该照片的用户
	 */
	public String selectByfaceid(String id){
		String sql = "select username from lost_population where faceid = ?";
		String username = null;
		try {
			con = JDBCUtil.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, id);
			System.out.println("---数据库的查询结果---");
			ResultSet rs = pstmt.executeQuery();
			if(!rs.next()){
				System.out.println("没有结果");
				return username;
			}else{
				rs.first();
			    username = rs.getString("username");
				System.out.println("匹配到的用户为: "+username);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JDBCUtil.CloseConnection(rs, pstmt, con);
		}
		return username;
	}
	/*
	 * 通过path路径查询数据
	 */
	public String selectByfacepath(String path){
		String sql = "select faceid from lost_population where facepath = ?";
		String faceid = "IsNull";
		try {
			    con = JDBCUtil.getConnection();
				pstmt = con.prepareStatement(sql);
				pstmt.setString(1, path);
				System.out.println("---数据库的查询结果---");
				ResultSet rs = pstmt.executeQuery();
				if(!rs.next()){
					System.out.println("没有结果faceid");
					return faceid;
				}else{
					rs.first();
				    faceid = rs.getString(1);
					System.out.println("路径为: "+path);
				}
		} catch (SQLException e) {
				e.printStackTrace();
		}finally{
			JDBCUtil.CloseConnection(rs, pstmt, con);
		}
			
		
		return faceid;
	}
	public void updateByid(String id,String path){
		String sql = "update lost_population set facepath = ? where faceid=?";
		try {
			con = JDBCUtil.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, path);
			pstmt.setString(2, id);
			int rtn = pstmt.executeUpdate();
			System.out.println("成功更新"+rtn+"条");
		} catch (SQLException e) {
			System.out.println("更新失败");
			e.printStackTrace();
		}finally{
			JDBCUtil.CloseConnection(rs, pstmt, con);
		}
		
	}
	public void updateBypath(String id,String path){
		String sql = "update lost_population set faceid = ? where facepath=?";
		try {
			con = JDBCUtil.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, id);
			pstmt.setString(2, path);
			int rtn = pstmt.executeUpdate();
			System.out.println("成功更新"+rtn+"条");
		} catch (SQLException e) {
			System.out.println("更新失败");
			e.printStackTrace();
		}finally{
			JDBCUtil.CloseConnection(rs, pstmt, con);
		}
	}
	public void deleteByid(String id){
		String sql = "delete from lost_population where faceid = ?";
		try {
			con = JDBCUtil.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, id);
			int rtn = pstmt.executeUpdate();
			System.out.println("成功删除"+rtn+"条记录");
		} catch (SQLException e) {
			System.out.println("删除失败");
			e.printStackTrace();
		}finally{
			JDBCUtil.CloseConnection(rs, pstmt, con);
		}
	}
	public void deleteBypath(String path){
		String sql = "delete from lost_population where facepath = ?";
		try {
			con = JDBCUtil.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, path);
			int rtn = pstmt.executeUpdate();
			System.out.println("成功删除"+rtn+"条记录");
		} catch (SQLException e) {
			System.out.println("删除失败");
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JDBCUtil.CloseConnection(rs, pstmt, con);
		}
	}
	/*
	 * 查询所有的数据
	 * 返回一个map容器，里面包含faceid和facepath
	 */
	public Map<String, String> selectAll(){
		Map<String,String> map = new HashMap<String, String>();
		String sql  = "select * from lost_population";
		try{
			con = JDBCUtil.getConnection();
			pstmt = con.prepareStatement(sql);
			rs  = pstmt.executeQuery();
			while(rs.next()){
				System.out.println("faceid为："+rs.getString("faceid")+"facepath为："+rs.getString("facepath"));
				map.put(rs.getString("faceid"), rs.getString("facepath"));
			}
		}catch(Exception e){
			System.out.println("查询失败");
			e.printStackTrace();
		}finally{
			JDBCUtil.CloseConnection(rs, pstmt, con);
		}
		return map;
	}
	
	
	public IdentityHashMap<String,String> selectUsernameByFaceid(List<String> faceList) {
		IdentityHashMap<String,String> usernames = new IdentityHashMap();
		String sql  = "select distinct username,name from lost_population where faceid in (";
		for(int i=0;i<faceList.size();i++){
			sql+="'"+faceList.get(i)+"'";
			if(i==faceList.size()-1){
				sql+=")";
			}else{
				sql+=",";
			}
		}
		try {
			con = JDBCUtil.getConnection();
			pstmt=con.prepareStatement(sql);
			rs=pstmt.executeQuery();
			while(rs.next()){
				usernames.put(rs.getString("username"), rs.getString("name"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JDBCUtil.CloseConnection(rs, pstmt, con);
		}
		return usernames;
	}
	public IdentityHashMap<String, String> selectUsernameAndFacepath(List<String> faceList) {
		IdentityHashMap<String,String> user = new IdentityHashMap<>();
		String sql  = "select username,facepath from lost_population where faceid in (";
		for(int i=0;i<faceList.size();i++){
			sql+="'"+faceList.get(i)+"'";
			if(i==faceList.size()-1){
				sql+=")";
			}else{
				sql+=",";
			}
		}
		try {
			con = JDBCUtil.getConnection();
			pstmt=con.prepareStatement(sql);
			rs=pstmt.executeQuery();
			while(rs.next()){
				user.put(rs.getString("username"), rs.getString("facepath"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JDBCUtil.CloseConnection(rs, pstmt, con);
		}
		return user;
	}
	public List<String> selectPathByUserNameAndName(String username, String name) {
		List<String> paths = new ArrayList<>();
		String sql = "select facepath from lost_population where username=? and name=?";
		try{
			con = JDBCUtil.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, username);
			pstmt.setString(2, name);
			rs = pstmt.executeQuery();
			while(rs.next()){
				paths.add(rs.getString("facepath"));
			}
		}catch(SQLException e){
			
		}finally{
			JDBCUtil.CloseConnection(rs, pstmt, con);
		}
		return paths;
	}
	public void deleteByUsernameAndName(String username, String name) {
		String sql = "delete from lost_population where username = ? and name=?";
		try {
			con = JDBCUtil.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, username);
			pstmt.setString(2, name);
			int rtn = pstmt.executeUpdate();
			System.out.println("成功删除"+rtn+"条记录");
		} catch (SQLException e) {
			System.out.println("删除失败");
			e.printStackTrace();
		}finally{
			JDBCUtil.CloseConnection(rs, pstmt, con);
		}
		
	}
	public Map<String, String> selectByUserNameAndName(String username,
			String name) {
		Map<String,String> map = new HashMap();
		String sql = "select faceid,facepath from lost_population where username=? and name=?";
		try{
			con = JDBCUtil.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, username);
			pstmt.setString(2, name);
			rs = pstmt.executeQuery();
			while(rs.next()){
				map.put(rs.getString("faceid"), rs.getString("facepath"));
			}
		}catch(SQLException e){
			
		}finally{
			JDBCUtil.CloseConnection(rs, pstmt, con);
		}
		return map;
	}
	public void updateName(String name, UsernameAndName usernameAndName) {
		String sql = "update lost_population set name = ? where username=? and name=?";
		try {
			con = JDBCUtil.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, name);
			pstmt.setString(2, usernameAndName.getUsername());
			pstmt.setString(3, usernameAndName.getName());
			int rtn = pstmt.executeUpdate();
			System.out.println("成功更新"+rtn+"条");
		} catch (SQLException e) {
			System.out.println("更新失败");
			e.printStackTrace();
		}finally{
			JDBCUtil.CloseConnection(rs, pstmt, con);
		}
		
	}
	
}
